《数据可视化分析2.0》第八章

各种分析工具都提供了大量内置函数（build-in function）从而简化数据准备计算。按照数据类型，可以分为字符串函数、日期函数、类型转化函数等。

字符串函数是最典型的行级别函数，完成数据清理、截取、拆分、查找、替换等操作。不同工具的字符串函数有类似之处，比如截取函数LEFT、RIGHT、MID，查找函数FIND、替换函数REPLACE等。Tableau常见的字符串函数可以分为如下小类：

截取是最简单的字符串函数，可以从字符串（String）中返回其中一部分即“子字符串”（Substring），也可以称之为“提取函数”。按照截取逻辑，可分为按长度截取、按分隔符截取和按通配符截取多个类型。

1）按照长度和位置截取

典型的截取函数是LEF、RIGHT和MID，分别用于从左侧、右侧、中间截取子字符串，几乎是各种数据处理工具（Excel、SQL、Python等）的标准内置函数。

截取函数的典型应用是从员工身份证ID中提取城市编码、出生日期、性别。

初学者要特别注意，从字符串中截取结果还是字符串数据类型，因此继续使用算数计算，还要把截取结果从默认字符串数据类型转化为整数（使用了INT转换），而后才能使用算术计算，比如以“余数是否为0”判断奇偶，偶数为女，奇数为男。后续会介绍INT、%余数计算、IIF逻辑判断函数。

（2）按照分隔符和位置截取

8.1节，使用Excel、SQL、Tableau从字段【产品名称】中截取【品牌】字段，如下所示。

这里SQL和Tableau的逻辑相近，先指定分隔符，后返回分隔符拆分后的指定部分。Tableau中拆分函数的完整语法是SPLIT([字符串], “分隔符”, 返回第几位)，比如：

SPLIT(“a-b-c-d”,”-“,2) = b

Tableau拆分函数被预置在字段右键菜单“变换”中，如图8-16所示。使用这种方法创建的字符串会自动添加TRIM函数——删除前后可能存在的空格。

（3）按照有规律的灵活规则（通配符）截取

如果要从字段【案情介绍】字符串中提取手机号、邮箱，或省市县等有特定规律的字符串，但是不能确定位置，也没有明确的分隔符可以判断，此时就要使用“高大上”的通配符（widecard character）。通配符是可以代表单个或者多个字符的特殊字符，常见的星号（*）代表多个字符，问号（?）代表单一字符。

本书会在8.4节单独介绍。

截取函数只是从字符串（String）中提取另一个字符串（Substring），可以理解为“查找+提取”两步操作。查找函数则是返回子字符串的位置，而替换则将指定子字符串替换为指定的字符串。查找函数返回结果不同，又可以分为以下分类。

（1）查找函数FIND——返回整数位置（从左侧开始的位置）。

Tableau中包括两个函数：

特别注意，FIND函数返回整数数字，没有则为空（null）；在视图中，度量默认会聚合，相当于行级别函数被聚合了，新用户特别容易在这里“翻车”，不过也是深刻领会行级别计算与聚合关系的契机，举例如下。

比如，从字段【订单ID】查找子字符串“20”，然后返回所在位置。如图8-17所示，在“列”中双击创建即席计算，输入 FIND( [订单ID],”20″)，每个订单ID就会返回一个整数数字。图中左侧是在数据表明细中的计算结果，而在右侧视图中，以【订单ID】为依据分组聚合了，订单“CN-2016-1070056”明细中有11行交易，所以聚合为44。

图8-16  FIND字符查找函数

Tableau的视图犹如Excel的透视表；在视图中，数字度量默认聚合。为了查看每个订单ID的情况，如图8-16右侧所示，在FIND函数字段上右击，在弹出的下拉菜单中将其从度量改为维度，连续并转化为离散，拖曳到订单ID之后显示，就构成了交叉表的样式。

可见，行级别计算既可以作为被聚合成为度量，也可以随时切换为维度。维度、度量是相对于问题而言的，因此是主观的。

FIND函数通常与LEFT、RIGHT等截取函数结合，比如“从左侧截取到第一个××字符”，这也是Excel中截取字符串字段的逻辑，表达式如下。

笔者在实施项目时，由于SAP HANA直连不支持拆分函数SPLIT，因此就使用FIND函数从字段【物料编码】间接提取每个部分，字段的格式为“一级物料—二级物料—三级物料”，截取“一级物料”如下。

LEFT([物料编码], FIND([物料编码],”-“)-1 )  //截取左侧到第一个短横分隔符

（2）包含函数CONTAINS——返回布尔值。

CONTAINS函数用于验证字段中是否包含被查找的子字符串，只是它返回是否而非位置，如果包含，则返回TRUE，否则返回FALSE。这个判断通常与逻辑判断结合，比如“当字符串中包含“××”字符串时，定义为‘危险’，否则‘正常’”：

在实践中，能用CONTAINS判断有无的，就不要用FIND加判断完成。

（3）指定位置判断是否包含——返回布尔值。

CONTAINS函数用于验证字符串中是否包含某个字符（完全不在乎位置），有时候需要缩小查找范围，仅仅查找是否以指定字符开头，或者以此结尾，这时就用STARTSWITH和ENDSWITH函数。比如字符串是否以“AA”开头，如果是，则返回TRUE。

STARTSWITH ([字符串字段],”AA”)       //字符串是否以“AA”字符开头

（4）替换函数REPLACE——返回字符串。

REPLACE ([字符串字段],”子字符串”,”用于替换的字符串”)

这个函数的用法简单清晰，把指定子字符串更换为一个新字符串。

笔者在公安局警情分析中遇到了这样的例子，“借助字符判断案件是否为涉财案件”。涉财案件的基本标准是，警情记录中一定会包含“涉嫌金额××元”“被盗财物××万元”等字样，因此可以按照以下的字段判断“涉财案件”。

不过，案件详情记录报警人的家庭住址、事发地点等，存在大量的“×号楼×单元”字样，这里的“单元”会影响上面的判断。比较简单的方案是先把案件详情中的“单元”临时修改，如下：

而后再判断是否包含代表货币的“元”，如下所示。

更进一步，使用逻辑函数IIF，把包含“元”的数据，标记为“涉财案件”，如图8-18所示。

图8-18  使用CONTAINS和REPLACE的实例

在没有其他异常字符的情况下，这个方案才可行；如果追求更高的可靠性，可以使用后续的“通配符函数”。规则越复杂，函数也随着越高级。

除了上述3类字符串函数，还有一些特殊的字符串函数可供使用，如下所示。

（1）空格函数SPACE(N)。

生成N个空格字符串，比如SPACE(2)生成两个空格，通常与其他函数结合使用。

（2）移除空格函数。

前面变换函数中自动添加了TRIM函数，它会移除字符串前后的空格，如下所示。

另外还有个比较延伸的函数，分别删除前导空格（L为LEFT）、删除尾随空格（R为RIGHT）。

（3）大小写切换函数：UPPER、LOWER。

分别把字符串改为大写或者小写（当然是英文字符），特别适合批量修改身份证尾号x为X。

（4）把每个单词都改为首字母大写：PROPER函数。

这个是2022.4版本的新函数，相当于UPPER、LOWER函数的组合使用，可以把数据值中的单词全部改为首字母大写、其他小写。特别适合于英文名称处理。比如：

（4）字符串长度函数：LEN函数。

返回字符串的长度。

日期是非常特殊的维度字段，它不仅具有连续性，而且自带层次特征，可以从年到季度、月要天、小时到分钟、分秒到毫秒一直切分下去。日期的层次性，是使用日期字段和创建日期函数的基础。

不同的数据库通常使用不同的日期格式，常见的如“2020-2-10 10:20:30”，任意的日期或时间都由多个日期部分构成，每个部分都有一个名称（datepart）对应。可以参考图8-19理解日期的层次结构和格式构成。

图8-19  日期的层次和离散/连续属性

日期自带层次结构，因此分析中可以调整日期的层次快速调整视图的分组聚合。常见的日期层次有年/季度/月/天（离散），还有年/年季度/年月/年周/年月日（连续），鼠标右击日期字段，可以在弹出的下拉菜单中快速调整。高级用户可以借助参数，实现年/季度/月等切换。

日期默认都是连续的，日期坐标轴会有一个明显特征：坐标轴分别向前、向后延伸一段距离，代表前后无限延伸。日期也可以改为离散显示，离散显示是为了更好地展示相互差异而非总体趋势。

Tableau的日期函数可以按照功能，分为以下几类。

一个完整的日期是由年、月、日组成的，而时间是由小时、分钟、秒组成的。既可以把多个散落在不同字段中的日期部分组成一个完成日期，也可以提取一个完整日期中的某个部分。

Tableau提供了多个函数把多个字段合并为一个完整日期、时间或者日期时间。其中，MAKEDATE(year,month,day)用于创建日期, MAKETIME(hour,minute,second)用于创建时间，这两个函数中的构成部分，必须是数字（整数），如图8-20所示。如果数据中没有代表“day”的数字，在“day”对应的函数位置输入1就可以代表各月月初。

图8-20  构建日期的函数MAKEDATE

在金融、仓库分析中，MAKEDATE常用于快速创建期初、期末日期，如下：

MAKEDATE(2022,X,1) 创建各月月初（X为1～12），和DATRTRUNC截取到月逻辑类似。

MAKEDATE(2022,X,0) 创建各月期初，即上月月末，对应DATRTRUNC截取到月减一天。除了MAKEDATE和MAKETIME函数，还有一个函数MAKEDATETIME(date,time)，它可以把独立的日期和时间两个字段合二为一，通常与上面两个函数结合使用。

另外两个特别重要的函数是TODAY()和NOW()。注意，这两个函数没有参数，分别返回当天的日期（比如2022-9-12）和当下的日期时间（比如2022-9-12 08:37:20）。

比如希望只查看当天的数据，可以创建如下的判断函数之后加入筛选器：

TODAY()和NOW()两个函数可以根据系统的日期自动变化，是动态筛选的好办法。业务分析还会使用TODAY()-1代表昨日——日期加减数字，代表天的加减计算。

另外，如果需要个日期常量，比如2022年9月12日，不必创建参数并为之设置初始值，Tableau中可以使用#2022-09-12#代表日期常量（注意月份必须两位）。这在仪表板开发测试时很常用。

日期连续、时序分析的前提是时间类的数据类型。在字符串上点击切换数据类型时，背后实际上两个函数，日期类型对应DATE函数，日期时间类型对应DATEIME函数，如图8-21上方所示。

点击字段更改类型适用于相对规范的字符串转换，复杂格式经常会转换失败，Tableau提供了专业转化函数DATEPARSE，将字符串映射为标准日期格式，英文PARSE是“解析、句法分析”之意。

使用DATEPARSE函数的关键是使用标准的字符组合来表示当前字符串的构成。国际上有一种标准化的日期解析标准，使用字母及位数代表字符串到日期的映射关系，比如M代表月，一个M代表1月，两个MM代表02月（包含前导零），三个MMM代表Feb，四个MMMM代表英文全称February。

日期解析函数DATEPARSE的常用符号对照表如表8-3所示。

表8-3  日期解析函数DATEPARSE的常用符号对照表[2]

如表8-4所示，这里举例如下，帮助读者进一步理解。

表8-4  常见的日期字符串及其解析规范

（1）日期部分Datepart和Datename

在数据明细中，经常见到多个日期字段，比如【订单日期】【订单年度】【订单月份】，其中的年度、月份是从【订单日期】中提取的一部分。在数据明细中存储大量的日期部分会占用数据库空间，因此可以在分析时随用随查，这就是日期提取函数DATEPART。对应的语法如下：

DATEPART( ‘date_part’,  [订单日期] )

在函数中指定date_part部分，比如year、quarter、month、day、week，就从【订单日期】中获得了对应的日期部分了。

Tableau之美，在于它把很多计算交互化了：双击日期默认按“年”分组，点击视图中的日期字段，可以随时转化为其他日期部分，甚至可以切换连续和离散显示，如图8-22所示。因此，把日期部分保存下来的必要性就比较低了。

图8-22  DATEPART函数：取日期的指定部分

为了进一步简化计算和嵌套，Tableau提供了多种简化函数，如下：

在一些特殊场景中，还可以使用dayofyear、weekday返回“全年第几数”和星期几。

Tableau在默认公历历法（格里高利历）之外，还支持ISO-8601规范，后者以周一为每周第一天，并且在每年第一周上有特别的处理方法，可以在视图的日期字段上点击切换。对应的日期部分函数是 ISOYEAR、ISOQUARTER、ISOMONTH、ISOWEEK。

DATEPART函数提取日期部分为数值（整数），另一个函数DATENAME(date_part,[标准日期字段])则可以提取为字符串。比如2016年 3月31日提取月度，返回的字符串结果就是“3月”，或者英文的“March”，如图8-23所示。

图8-23  DATENAME函数提取日期某个部分的名称

要注意二者区别，DATEPART函数返回结果为整数，可以根据需要设置多种显示格式；DATENAME的结果是字符串，因此不能设置格式。

（2）日期截取DATETRUNC

如果要从订单日期中统计“年月”的销售额，此时就需要提取两个日期部分，或者说从年截取到月的部分。此时就有了连续截取函数DATETRUNC。

TRUNC是英文TRUNCATE（截断、裁断）的缩写，DATETRUNC将日期截取到相应的连续部分，比如2019年1月5日和2019年1月10日截取到“月”，都是2019年1月1日。裁断后的日期依然是完全连续的，区别于DATEPART和DATENAME。

和DATEPART类似，连续的日期截取也可以在视图中点击完成，如图8-24所示，以中间的线为分割，上面的是DATEPART日期部分（默认离散），下面是DATETRUNC日期截取（默认连续）。

图8-24  在视图中点击转换为日期截取函数DATETRUNC

这里要特别说明的是，Tableau实际上把DATEPART和DATETRUNC函数都内置到了“创建→自定义日期”之中了。如图8-25所示，右击“发货日期”字段，在弹出的下拉菜单中选择“创建→自定义日期”命令，可以截取日期的指定部分。这里的“日期部分”对应DATEPART日期部分函数，而“日期值”对应DATETRUNC连续截取函数。

不过，笔者强烈不推荐使用“自定义日期”中的“年/月”生成日期部分。日期部分是DATEPART，“年/月”的日期部分是两个DATEPART算术计算而来，且预设离散、字符串显示。如上图8-25右侧所示，这容易给初学者带来误导，不利于理解数据类型和字段分类。读者要理解下面两种计算的区别。

图8-25  使用预置“自定义日期”创建多个日期字段（不推荐）

DATETRUNC计算确保了结果的“日期”数据类型，因此可以和其他日期直接比较，在视图中可以根据需要切换连续、离散显示，保留了它的灵活性。

这个写法常被用来计算日期对应的“期初日期”，比如仓储进销存、财务期间、金融期初等。本月的期初值是上月月末，结合简单计算即可获得。

除了“天”的差异可以直接加减，其他的部分就要使用日期计算函数了。

日期计算包含两种情景：两个日期计算间隔时间、日期和数字计算。对应两个日期函数：DATEDIFF和DATEADD。

DATEDIFF( ’day’ , [订单日期], [发货日期])  // 订单到发货的间隔天数

DATEADD( ’day’ , 6, [订单日期])  // 订单日期增加6天

DATEDIFF函数的关键是指定间隔单位，支持多个参数，如图8-26所示。

图8-26  日期计算函数DATEDIFF

DATEADD函数与之类似，相当于在指定日期上增加一个特定的时长。

举例，有不少单位将每月26日（含）之后的业绩算到次月统计，与其修改数据采集系统，不如在Tableau中创建一个辅助字段“统计月份”，为符合条件的“订单日期”增加若干天到下个月，逻辑过程和语法如图8-27所示。

图8-27  DATEADD函数的示例

考虑到不同月份的天数不同，不足31天的会出现25日也到下个月的情况，因此可以结合DATEPART或DAY函数、逻辑判断进一步完善上述过程，并用DATE转换数据类型，如下所示。

这样，使用新字段的“年月”部分作为统计月份了。在日期计算中，以“天”为间隔或单位的计算，可以直接使用加减法，另外建议仅保留计算中有效的部分，因此还可以进一步完善，如下所示。

DATEPART函数是最常用的日期函数，在日期筛选时也应用广泛。这里分享两个案例：

（1）筛选历史年度“年初至今”同期数据[3]

比如，要计算今年“年初至今”的销售额与去年同期的差异，需要对销售明细做“同期筛选”。如图8-28所示，这里有两种筛选方法：（1）历史年度的数据，保留到“同月同日”，（2）历史年度的数据，保留到相同的天数（比如第169天）。统计上看，后者可以保证经营天数完全相同。

假设当下日期是6月18日。第一种方法的筛选标准是就是：之前月份（1～5月）全部保留，当月（6月）保留到6月18日。借助于MONTH和DAY函数，可以使用如下判断完成：